import pymssql
from datetime import datetime, timedelta


class sqlDB():

    def __init__(self):
        try:
            self.conn = pymssql.connect(host='127.0.0.1',
                                        user='sa',
                                        password='Spd13579',
                                        database='zjszdzdb',
                                        charset='utf8')
            self.cursor = self.conn.cursor()

        except Exception as ex:
            self.conn.rollback()
            raise ex

    def select(self, base_time, timerange=(0, 6)):
        # stimes = []
        # for mi in range(timerange):
        #     stimes.append((datetime.strptime(start_time, '%Y%m%d%H%M') - timedelta(minutes=mi)).strftime('%Y%m%d%H%M'))
        #
        # code = f"SELECT StationNum, EEEee, NNnn, Precipitation from (SELECT StationNum, SUM(Precipitation) AS Precipitation FROM [dbo].[查询{start_time[:4]}] WHERE ObservTimes IN {tuple(stimes)} GROUP BY StationNum ) AS A LEFT JOIN [站点信息] as B ON A.StationNum = B.IIiii ORDER BY StationNum"

        start_time = (datetime.strptime(base_time, '%Y%m%d%H%M') + timedelta(minutes=timerange[0])).strftime('%Y%m%d%H%M')
        end_time = (datetime.strptime(base_time, '%Y%m%d%H%M') + timedelta(minutes=timerange[1])).strftime('%Y%m%d%H%M')

        code = f"SELECT StationNum, EEEee, NNnn, Precipitation from (SELECT StationNum, SUM(Precipitation) AS Precipitation FROM [dbo].[查询{start_time[:4]}] WHERE ObservTimes BETWEEN {start_time} AND {end_time} GROUP BY StationNum ) AS A LEFT JOIN [站点信息] as B ON A.StationNum = B.IIiii ORDER BY StationNum"

        self.cursor.execute(code)
        rs = self.cursor.fetchall()
        self.conn.commit()
        return rs

    def close(self):
        self.conn.close()
